/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.pps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import vn.com.pps.entities.Pupil;
import org.apache.log4j.Logger;

/**
 *
 * @author Dai
 */
public class PupilDAO extends BaseDAO implements IPupilDAO {

    private Logger logger = Logger.getLogger(PupilDAO.class);

    @Override
    public int getCount() {
        String sqlQuery = "SELECT COUNT(*) FROM TblPupils";
        return getJdbcTemplate().queryForInt(sqlQuery);
    }

    @Override
    public List<Pupil> getPupils() {
        String sqlQuery = "SELECT * FROM TblPupils";
        return getJdbcTemplate().query(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Pupil pupil = new Pupil();
                pupil.setId(rs.getInt("ID"));
                pupil.setFirstName(rs.getString("Firstname"));
                pupil.setLastName(rs.getString("Lastname"));
                pupil.setMobile(rs.getString("Mobile"));
                pupil.setMother(rs.getString("Mother"));
                pupil.setFather(rs.getString("Father"));
                pupil.setYearOfBirth(rs.getInt("YearOfBirth"));
                return pupil;
            }
        });
    }

    @Override
    public Page<Pupil> getPupils(int pageNo, int pageSize, String whereClause) {
        String sqlQuery = "SELECT * FROM TblPupils WHERE " + whereClause;
        String sqlQueryCount = "SELECT COUNT(*) FROM TblPupils WHERE " + whereClause;
        logger.info("SQLQuery" + sqlQuery);
        PaginationHelper<Pupil> ph = new PaginationHelper<Pupil>();
        return ph.fetchPage(
                this.getJdbcTemplate(),
                sqlQueryCount,
                sqlQuery,
                null,
                pageNo,
                pageSize, new ParameterizedRowMapper<Pupil>() {
                    @Override
                    public Pupil mapRow(ResultSet rs, int i) throws SQLException {
                        Pupil pupil = new Pupil();
                        pupil.setId(rs.getInt("ID"));
                        pupil.setFirstName(rs.getString("Firstname"));
                        pupil.setLastName(rs.getString("Lastname"));
                        pupil.setMobile(rs.getString("Mobile"));
                        pupil.setMother(rs.getString("Mother"));
                        pupil.setFather(rs.getString("Father"));
                        pupil.setYearOfBirth(rs.getInt("YearOfBirth"));
                        return pupil;
                    }
                });
    }

    @Override
    public Pupil getPupil(Integer id) {
        String sqlQuery = "SELECT * FROM TblPupils WHERE ID=" + id;
        return (Pupil) getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Pupil pupil = new Pupil();
                pupil.setId(rs.getInt("ID"));
                pupil.setFirstName(rs.getString("Firstname"));
                pupil.setLastName(rs.getString("Lastname"));
                pupil.setMobile(rs.getString("Mobile"));
                pupil.setMother(rs.getString("Mother"));
                pupil.setFather(rs.getString("Father"));
                pupil.setYearOfBirth(rs.getInt("YearOfBirth"));
                return pupil;
            }
        });
    }

    @Override
    public int insert(Pupil pupil) {
        String sql = "INSERT INTO TblPupils(ID,Firstname,Lastname,Mobile,Mother,Father,YearOfBirth,Note,CreatedOn,LastModified) VALUES(?,?,?,?,?,?,?,?,?,?)";
        Object[] params = new Object[]{null, pupil.getFirstName(), pupil.getLastName(),
            pupil.getMobile(), pupil.getMother(), pupil.getFather(), pupil.getYearOfBirth(),
            pupil.getNote(), pupil.getCreatedDate(), pupil.getLastModifiedDate()
        };
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        psc.addParameter(new SqlParameter("Firstname", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Lastname", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Mobile", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Mother", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Father", Types.VARCHAR));
        psc.addParameter(new SqlParameter("YearOfBirth", Types.INTEGER));
        psc.addParameter(new SqlParameter("Note", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CreatedOn", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.setReturnGeneratedKeys(true);
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params),
                generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
    }

    @Override
    public int update(Pupil pupil) {
        String sql = "UPDATE TblPupils SET Firstname=?,Lastname=?,Mobile=?,Mother=?,Father=?,YearOfBirth=?,Note=?,LastModified=? WHERE ID=?";

        Object[] params = new Object[]{pupil.getFirstName(), pupil.getLastName(),
            pupil.getMobile(), pupil.getMother(), pupil.getFather(), pupil.getYearOfBirth(),
            pupil.getNote(), pupil.getLastModifiedDate(), pupil.getId()
        };
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);

        psc.addParameter(new SqlParameter("Firstname", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Lastname", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Mobile", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Mother", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Father", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Note", Types.VARCHAR));
        psc.addParameter(new SqlParameter("YearOfBirth", Types.INTEGER));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params));
        // getJdbcTemplate().update(sql);
        return pupil.getId();
    }

    @Override
    public int getCount(String whereClause) {
        String sqlQueryCount = "SELECT COUNT(*) FROM TblPupils WHERE " + whereClause;
        return getJdbcTemplate().queryForInt(sqlQueryCount);
    }

    @Override
    public int delete(int id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public boolean findPupilbyPhone(String phone) {
        String sqlQueryCount = "SELECT COUNT(*) FROM TblPupils WHERE mobile='" + phone + "'";
        return getJdbcTemplate().queryForInt(sqlQueryCount) == 0 ? false : true;
    }

}
